"""
auth : ginger

    封装 openpyxl
        读取 Excel
        写入 Excel ( list )
        清空 Excel
"""
import openpyxl


class DoExcel:
    def __init__(self, file_name, sheet_name):
        self.file_name = file_name
        self.sheet_name = sheet_name

    def read(self):
        wb = openpyxl.load_workbook(self.file_name)
        s = wb[self.sheet_name]

        max_row = s.max_row
        max_col = s.max_column

        list_all = []
        for i in range(1, max_row + 1):
            list_row = []
            for j in range(1, max_col + 1):
                list_row.append(s.cell(i, j).value)
            list_all.append(list_row)

        return list_all

    """
    def write(self, row, column, value):
        wb = openpyxl.load_workbook(self.file_name)
        s = self.wb[self.sheet_name]
        s.cell(row, column).value = value
        s.save()
        wb.save(self.file_name)
    """

    def write_list(self, list_datas):
        """
        :param list_datas: 列表嵌套元组,每个元组是一行数据
        :return:
        """

        self.excel_data_clear()

        wb = openpyxl.load_workbook(self.file_name)
        s = wb[self.sheet_name]

        row = 1
        for item_tuple in list_datas:
            row += 1
            column = 0
            for item_cell_value in item_tuple:
                column += 1
                s.cell(row, column).value = item_cell_value
        try:
            wb.save(self.file_name)
        except PermissionError:
            print('请关闭 Excel 后再运行...')

    def excel_data_clear(self):
        wb = openpyxl.load_workbook(self.file_name)
        s = wb.active

        row = s.max_row
        column = s.max_column
        for i in range(2, row + 1):
            for j in range(1, column + 1):
                s.cell(row=i, column=j, value="")
        wb.save(self.file_name)


if __name__ == '__main__':
    from common.do_file_path import EXCEL_PATH
    from services.services import get_all_report_table_s

    file___name = EXCEL_PATH
    sheet__name = 'Jobs'
    de = DoExcel(file___name, sheet__name)

    # """
    lists_data_s = get_all_report_table_s()
    de.write_list(lists_data_s)
    # """

    """
    data_s = de.read()
    for i in data_s:
        if i[0]:
            print(i)
    """
